﻿/*
 * Migrates the race results from the old database format to the new.
 */

/*
 * Step 1: Import the tables from Access into SQL using SQL Managemetn Studio Data Import wizard. You should end up
 * with two tables TRL_RACES and TRL_RACES_RESULTS
 */

/*
 * Step 2: Run the SQL that copies data from the two access tables into our new tables: Races and RaceParticipants
 */

SET IDENTITY_INSERT [trladmin].[dbo].[Races]

INSERT INTO [trladmin].[dbo].[Races]
   ([raceId]
   ,[name]
   ,[dateOfRace]
   ,[city]
   ,[region])
SELECT 
    [R_ID],
    [R_NAME], 
    [R_TDSTAMP], 
    NULL, 
    NULL,   
    -- What do I do with r_details and r_distance?
FROM TRL_RACES




SELECT r_details, r_distance, * FROM TRL_RACES

        (
        ,
        ,[R_DETAILS]
        ,[R_RACERS]
        ,
        ,[R_DISTANCE])     
     
           (<name, varchar(100),>
           ,<dateOfRace, date,>
           ,<city, varchar(100),>
           ,<region, char(2),>)
GO





INSERT INTO [trladmin].[dbo].[RaceParticipants]
           ([raceId]
           ,[name]
           ,[time]
           ,[racePlace]
           ,[gender]
           ,[legacyGenderPlace])
     VALUES
           (<raceId, int,>
           ,<name, varchar(100),>
           ,<time, time(7),>
           ,<racePlace, int,>
           ,<gender, char(1),>
           ,<legacyGenderPlace, int,>)
GO



INSERT INTO [trladmin].[dbo].[TRL_RACES]

    VALUES
        (<R_ID, int,>
        ,<R_NAME, nvarchar(50),>
        ,<R_DETAILS, nvarchar(max),>
        ,<R_RACERS, int,>
        ,<R_TDSTAMP, datetime,>
        ,<R_DISTANCE, nvarchar(10),>)
GO

 INSERT INTO [trladmin].[dbo].[TRL_RACES_RESULTS]
        ([RR_ID]
        ,[RR_NAME]
        ,[RR_TIME]
        ,[RR_PLACE]
        ,[RR_PLACENUM]
        ,[R_ID])
    VALUES
       (<RR_ID, int,>
       ,<RR_NAME, nvarchar(75),>
       ,<RR_TIME, nvarchar(20),>
       ,<RR_PLACE, int,>
       ,<RR_PLACENUM, int,>
       ,<R_ID, int,>)
GO





